package com.gisuni.sddc.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.nutz.log.Log;
import org.nutz.log.Logs;
import org.json.JSONArray;
import org.json.JSONObject;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import com.gisuni.sddc.dao.LandStatisticsDao;
import com.gisuni.sddc.model.Response;
import com.gisuni.sddc.platformManager.dao.LayerTableNameDao;
import com.gisuni.sddc.util.ExcelOperation;
import com.gisuni.sddc.util.StringUtil;

@IocBean
public class LandStatisticsService {

	private Log log = Logs.getLog(LandStatisticsService.class);
	@Inject
	private LandStatisticsDao landStatisticsDao;
	@Inject
    private LayerTableNameDao layerTableNameDao; 
	/**
	 * 查询每个组织关系
	 * 
	 * @param
	 * @return
	 */
	public Response queryOrganizationList(String departmentType, String projectType, String startdate, String enddate) {

		Response response = new Response();
		response.setSuccess(false);
		String OrgResult = null;
		String LandResult = null;
		StringBuffer selectstr = new StringBuffer("");
		try {
					/**
					 * select tc.organization_name company,
					       (select tu.displayname
					          from SDDCONEMAP.USERINFO tu
					         where tu.userid = tc.organization_manager_id) companyleader,
					       tb.organization_name department,
					       (select tu.displayname
					          from SDDCONEMAP.USERINFO tu
					         where tu.userid = tb.organization_manager_id) departmentleader,
					       tt.organization_name team,
					       (select tu.displayname
					          from SDDCONEMAP.USERINFO tu
					         where tu.userid = tt.organization_manager_id) teamleader
					  from SDDCGISPLATFORM.ORGANIZATION tt,
					       SDDCGISPLATFORM.ORGANIZATION tb,
					       SDDCGISPLATFORM.ORGANIZATION tc
					 where tt.parent_id = tb.id
					   and tb.parent_id = tc.id
					
					 */
			
			 selectstr = new StringBuffer("select tc.organization_name company,tc.id companyOrgId,(select tu.displayname  ");
			 selectstr.append("from SDDCONEMAP.USERINFO tu ");
					 selectstr.append("where tu.userid=tc.organization_manager_id)companyleader,tb.organization_name department,tb.id deptOrgId,(select tu.displayname ");
							 selectstr.append("from SDDCONEMAP.USERINFO tu ");
									 selectstr.append("where tu.userid=tb.organization_manager_id)departmentleader,tt.organization_name team,tt.id developOrgId,(select tu.displayname ");
											 selectstr.append("from SDDCONEMAP.USERINFO tu where tu.userid=tt.organization_manager_id)teamleader ");
													 selectstr.append("from SDDCGISPLATFORM.ORGANIZATION tt,SDDCGISPLATFORM.ORGANIZATION tb,SDDCGISPLATFORM.ORGANIZATION tc ");
															 selectstr.append("where tt.parent_id=tb.id and tb.parent_id=tc.id");
			OrgResult = landStatisticsDao.queryResultList(selectstr.toString());

			if (OrgResult == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(OrgResult);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}

	/**
	 * 统计制定时间段内各拓展组的登记情况   报表
	 * 
	 * @param
	 * @return
	 */
	public Response queryLandList(String projectType, String startdate, String enddate) {

		Response response = new Response();
		response.setSuccess(false);
		String Result = null;
		String selectstr = "";
		Map<String, String> map  = layerTableNameDao.getTableName();

		
		try {

//			selectstr = "select t.objectid,                                          "
//					+ "       t.xh,                                                "
//					+ "       t.type,                                              "
//					+ "       tz.organization_name team,                           "
//					+ "       tb.organization_name department,                     "
//					+ "       tc.organization_name company                         "
//					+ "  from SDE.LANDEXPAND        t,                                 "
//					+ "       SDDCGISPLATFORM.USER_ORGANIZATION t2,                                "
//					+ "       SDDCONEMAP.USERINFO          t1,                                "
//					+ "       SDDCGISPLATFORM.ORGANIZATION      tz,                                "
//					+ "       SDDCGISPLATFORM.ORGANIZATION      tb,                                "
//					+ "       SDDCGISPLATFORM.ORGANIZATION      tc                                 "
//					+ " where t.registeruser = t2.user_id(+)                       "
//					+ "   and t.registeruser = t1.userid(+)                        "
//					+ "   and tz.id = t2.organization_develop_id                   "
//					+ "   and tb.id = t2.organization_business_id                  "
//					+ "   and tc.id = t2.organization_company_id                   " + "   and t.type = '" + projectType
//					+ "'                           " + "   and t.registerdate >= to_date('" + startdate
//					+ "', 'yyyy-mm-dd')" + "   and t.registerdate <= to_date('" + enddate + "', 'yyyy-mm-dd') "
//					+ "order by tc.organization_name,tb.organization_name,tz.organization_name";
			
			selectstr = "select t.objectid xh,                                          "
//					+ "       t.xh,                                                "
					+ "       t.type,                                              "
					+ "       tz.organization_name team,                           "
					+ "       tb.organization_name department,                     "
					+ "       tc.organization_name company                         "
//					+ "  from (SELECT US.USER_ID registeruser,WZ.DJRQ registerdate,WZ.XMJD TYPE,WZ.OBJECTID   FROM SDDCGISDATA.WYTZ WZ LEFT JOIN SDDCGISPLATFORM.USER_INFO US ON WZ.USERNAME = US.USER_NAME )        t,                                 "
					+ "  from (SELECT US.USER_ID registeruser,WZ.DJRQ registerdate,WZ.XMJD TYPE,WZ.OBJECTID   FROM "+map.get("WYTZ")+" WZ LEFT JOIN SDDCGISPLATFORM.USER_INFO US ON WZ.USERNAME = US.USER_NAME )        t,                                 "

					+ "       SDDCGISPLATFORM.USER_ORGANIZATION t2,                                "
					+ "       SDDCONEMAP.USERINFO          t1,                                "
					+ "       SDDCGISPLATFORM.ORGANIZATION      tz,                                "
					+ "       SDDCGISPLATFORM.ORGANIZATION      tb,                                "
					+ "       SDDCGISPLATFORM.ORGANIZATION      tc                                 "
					+ " where t.registeruser = t2.user_id(+)                       "
					+ "   and t.registeruser = t1.userid(+)                        "
					+ "   and tz.id = t2.organization_develop_id                   "
					+ "   and tb.id = t2.organization_business_id                  "
					+ "   and tc.id = t2.organization_company_id                   " + "   and t.type = '" + projectType
					+ "'                           " + "   and t.registerdate >= to_date('" + startdate
					+ "', 'yyyy-mm-dd')" + "   and t.registerdate <= to_date('" + enddate + "', 'yyyy-mm-dd') "
					+ "order by tc.organization_name,tb.organization_name,tz.organization_name";
			
			Result = landStatisticsDao.queryResultList(selectstr);

			if (Result == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}

	/**
	 * 统计制定时间段内各拓展组的登记情况
	 * 
	 * @param
	 * @return
	 */
	public Response queryAndStatisticsLandByTeam(String departmentType, String projectType, String startdate,
			String enddate) {

		Response response = new Response();
		response.setSuccess(false);
		String Result = null;
		String selectstr = "";
		try {

			selectstr = " select tf.company,tf.department,tf.team,th.teamleader,th.pnum,'2' zhibiao,(NVL(th.pnum,0)/2)*100 wcd from     "
					+ " (select tc.organization_name company,tb.organization_name department,tt.organization_name team ,tt.id teamid from  "
					+ "          SDDCGISPLATFORM.ORGANIZATION tt,                                                                                          "
					+ "          SDDCGISPLATFORM.ORGANIZATION tb,                                                                                          "
					+ "          SDDCGISPLATFORM.ORGANIZATION tc                                                                                           "
					+ "    where tt.parent_id = tb.id                                                                                      "
					+ "      and tb.parent_id = tc.id) tf,                                                                                 "
					+ " (select tg.company,tg.department,tg.teamid,tg.team,tg.teamleader,count(tg.team) pnum from                          "
					+ "    (select t.objectid,                                                                                             "
					+ "          t.xh,                                                                                                     "
					+ "          t.type,                                                                                                   "
					+ "          t.registeruser,                                                                                           "
					+ "          t1.displayname,                                                                                           "
					+ "          tz.organization_name team,                                                                                "
					+ "          tz.id teamid,                                                                                             "
					+ "          (select tu.displayname                                                                                    "
					+ "             from SDDCONEMAP.USERINFO tu                                                                                       "
					+ "            where tu.userid = tz.organization_manager_id) teamleader,                                               "
					+ "          tb.organization_name department,                                                                          "
					+ "          tb.id departmentid,                                                                                       "
					+ "          tc.organization_name company,                                                                             "
					+ "          tc.id companyid                                                                                           "
					+ "     from SDE.LANDEXPAND t,                                                                                             "
					+ "          SDDCGISPLATFORM.USER_ORGANIZATION t2,                                                                                     "
					+ "          SDDCONEMAP.USERINFO t1,                                                                                              "
					+ "          SDDCGISPLATFORM.ORGANIZATION tz,                                                                                          "
					+ "          SDDCGISPLATFORM.ORGANIZATION tb,                                                                                          "
					+ "          SDDCGISPLATFORM.ORGANIZATION tc                                                                                           "
					+ "    where t.registeruser = t2.user_id(+)                                                                            "
					+ "      and t.registeruser = t1.userid(+)                                                                             "
					+ "      and tz.id = t2.organization_develop_id                                                                        "
					+ "      and tb.id = t2.organization_business_id                                                                       "
					+ "      and tc.id = t2.organization_company_id                                                                        "
					+ "      and t.type = '" + projectType + "'                                         "
					+ "      and t.registerdate >= to_date('" + startdate + "', 'yyyy-mm-dd')           "
					+ "      and t.registerdate <= to_date('" + enddate + "', 'yyyy-mm-dd')) tg         "
					+ "      group by tg.teamid,tg.team,tg.teamleader,tg.department,tg.company) th                                         "
					+ "      where tf.teamid = th.teamid(+) order by wcd desc                                                              ";
			Result = landStatisticsDao.queryAndStatisticsLandByTeam(selectstr);

			if (Result == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}

	/**
	 * 统计制定时间段内各事业部的登记情况
	 * 
	 * @param
	 * @return
	 */
	public Response queryAndStatisticsLandByDepartment(String departmentType, String projectType, String startdate,
			String enddate) {

		Response response = new Response();
		response.setSuccess(false);
		String Result = null;
		String selectstr = "";
		try {

			selectstr = "  select tf.company,tf.department,tf.gnum, tt.pnum,tf.gnum*2 zhibiao,ROUND((NVL(tt.pnum,0)/(tf.gnum*2))*100,1) wcd  from    "
					+ " (select t2.organization_name company, t1.organization_name department,t1.id departmentid,tb.gnum                                       "
					+ "                  from SDDCGISPLATFORM.ORGANIZATION t1,                                                                                                 "
					+ "                       SDDCGISPLATFORM.ORGANIZATION t2,                                                                                                 "
					+ "                       (select t3.parent_id, count(t3.parent_id) gnum                                                                   "
					+ "                          from SDDCGISPLATFORM.ORGANIZATION t3                                                                                          "
					+ "                         where t3.organization_type = '3'                                                                               "
					+ "                         group by t3.parent_id) tb                                                                                      "
					+ "                 where t1.id = tb.parent_id                                                                                             "
					+ "                   and t2.id = t1.parent_id                                                                                             "
					+ "                   and t1.organization_type = '2')tf,                                                                                   "
					+ " (select tg.company,tg.department,tg.departmentid,count(tg.department) pnum from                                                        "
					+ "    (select t.objectid,                                                                                                                 "
					+ "          t.xh,                                                                                                                         "
					+ "          t.type,                                                                                                                       "
					+ "          t.registeruser,                                                                                                               "
					+ "          t1.displayname,                                                                                                               "
					+ "          tb.organization_name department,                                                                                              "
					+ "          tb.id departmentid,                                                                                                           "
					+ "          tc.organization_name company                                                                                                  "
					+ "     from SDE.LANDEXPAND t,                                                                                                                 "
					+ "          SDDCGISPLATFORM.USER_ORGANIZATION t2,                                                                                                         "
					+ "          SDDCONEMAP.USERINFO t1,                                                                                                                  "
					+ "          SDDCGISPLATFORM.ORGANIZATION tz,                                                                                                              "
					+ "          SDDCGISPLATFORM.ORGANIZATION tb,                                                                                                              "
					+ "          SDDCGISPLATFORM.ORGANIZATION tc                                                                                                               "
					+ "    where t.registeruser = t2.user_id(+)                                                                                                "
					+ "      and t.registeruser = t1.userid(+)                                                                                                 "
					+ "      and tz.id = t2.organization_develop_id                                                                                            "
					+ "      and tb.id = t2.organization_business_id                                                                                           "
					+ "      and tc.id = t2.organization_company_id                                                                                            "
					+ "      and t.type = '" + projectType + "'                                         "
					+ "      and t.registerdate >= to_date('" + startdate + "', 'yyyy-mm-dd')           "
					+ "      and t.registerdate <= to_date('" + enddate
					+ "', 'yyyy-mm-dd')) tg                                                                     "
					+ "      group by tg.department,tg.departmentid,tg.company) tt                                                                             "
					+ "      where tf.departmentid = tt.departmentid(+) order by wcd desc                                                                     ";

			Result = landStatisticsDao.queryAndStatisticsLandByTeam(selectstr);

			if (Result == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}

	/**
	 * 统计制定时间段内各集团公司的登记情况
	 * 
	 * @param
	 * @return
	 */
	public Response queryAndStatisticsLandByCompany(String departmentType, String projectType, String startdate,
			String enddate) {

		Response response = new Response();
		response.setSuccess(false);
		String Result = null;
		String selectstr = "";
		try {

			selectstr = "  select tf.organization_name company,tf.gnum,th.pnum,tf.gnum*2 zhibiao, ROUND((NVL(th.pnum,0)/(tf.gnum*2))*100,1) wcd "
					+ "  from (select t5.*, t4.organization_name                                                                                       "
					+ "             from (select t.parent_id, sum(t.gnum) gnum                                                                         "
					+ "                     from (select t1.id,                                                                                        "
					+ "                                  t1.organization_name,                                                                         "
					+ "                                  t1.parent_id,                                                                                 "
					+ "                                  tb.gnum                                                                                       "
					+ "                             from SDDCGISPLATFORM.ORGANIZATION t1,                                                                              "
					+ "                                  (select t3.parent_id,                                                                         "
					+ "                                          count(t3.parent_id) gnum                                                              "
					+ "                                     from SDDCGISPLATFORM.ORGANIZATION t3                                                                       "
					+ "                                    where t3.organization_type = '3'                                                            "
					+ "                                    group by t3.parent_id) tb                                                                   "
					+ "                            where t1.id = tb.parent_id                                                                          "
					+ "                              and t1.organization_type = '2') t                                                                 "
					+ "                    group by t.parent_id) t5,                                                                                   "
					+ "                  ORGANIZATION t4                                                                                               "
					+ "            where t4.id = t5.parent_id) tf,                                                                                     "
					+ "  (select tg.companyid,tg.company,count(tg.company) pnum from                                                                   "
					+ "    (select tc.id companyid,                                                                                                    "
					+ "    tc.organization_name company                                                                                                "
					+ "     from SDE.LANDEXPAND t,                                                                                                         "
					+ "          SDDCGISPLATFORM.USER_ORGANIZATION t2,                                                                                                 "
					+ "          SDDCONEMAP..USERINFO t1,                                                                                                          "
					+ "          SDDCGISPLATFORM.ORGANIZATION tc                                                                                                       "
					+ "    where t.registeruser = t2.user_id(+)                                                                                        "
					+ "      and t.registeruser = t1.userid(+)                                                                                         "
					+ "      and tc.id = t2.organization_company_id                                                                                    "
					+ "      and t.type = '" + projectType + "'                                         "
					+ "      and t.registerdate >= to_date('" + startdate + "', 'yyyy-mm-dd')           "
					+ "      and t.registerdate <= to_date('" + enddate
					+ "', 'yyyy-mm-dd')) tg                                                            "
					+ "      group by tg.companyid,tg.company) th                                                                                      "
					+ "      where tf.parent_id = th.companyid(+) order by wcd desc                                                                                     ";
			Result = landStatisticsDao.queryAndStatisticsLandByTeam(selectstr);

			if (Result == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}

	/**
	 * 导出Excel
	 * @param departmentType
	 * @param projectType
	 * @param startdate
	 * @param enddate
	 * @param response
	 * @throws Exception
	 */
	public void exportlandStatistics(String departmentType, String projectType, String startdate, String enddate,
			HttpServletResponse response) throws Exception {
		
		String filetitle = null;
		String[] rowsName = null;
		String[] columnTop = null;
		String rowTitle="";
		if("mocha".equals(projectType)){
			projectType="核查项目";
			rowTitle="摸查地块数";
		}else if("qiatan".equals(projectType)){
			projectType="洽谈项目";
			rowTitle="洽谈项目数";
		}else if("huoqu".equals(projectType)){
			projectType="获取项目";
			rowTitle="获取项目数";
		}
		Response responseStr = getExportResponse(departmentType, projectType, startdate, enddate);
		if ("company".equals(departmentType)) {
			filetitle = "公司、集团"+projectType;
			rowsName = new String[] { "排名", "城市","负责人",   rowTitle, "任务指标", "完成度" };
			columnTop = new String[] { "", "COMPANY", "TEAMLEADER",  "PNUM", "ZHIBIAO", "WCD" };
		} else if ("department".equals(departmentType)) {
			filetitle = "事业部"+projectType;
			rowsName = new String[] { "排名", "城市","事业部","负责人","小组数",   rowTitle, "任务指标", "完成度" };
			columnTop = new String[] { "", "COMPANY", "DEPARTMENT","TEAMLEADER","GNUM",  "PNUM", "ZHIBIAO", "WCD" };
		} else if ("teamgroup".equals(departmentType)) {
			filetitle = "扩展组"+projectType;
			rowsName = new String[] { "排名", "城市", "事业部","扩展组", "负责人", rowTitle, "任务指标", "完成度" };
			columnTop = new String[] { "", "COMPANY", "DEPARTMENT","TEAM","TEAMLEADER",  "PNUM", "ZHIBIAO", "WCD" };
		}
//		if(responseStr.getResult().)
		String resultStr = (String) responseStr.getResult();
		JSONArray resultJsonArr = new JSONArray(resultStr);
		resultJsonArr.length();
		List<Map> Result = new ArrayList<Map>();
		if (resultJsonArr != null && resultJsonArr.length() > 0) {

			for (int i = 0; i < resultJsonArr.length(); i++) {
				JSONObject ret = (JSONObject) resultJsonArr.get(i);
				Iterator iterator = ret.keys();
				Map map = new HashMap();
				while (iterator.hasNext()) {
					String key = (String) iterator.next();
					String value = (String) ret.get(key);
					if("WCD".equals(key)){
				      value=value+"%";
					}
					map.put(key, value);
				}
				Result.add(map);
			}
		}
		ExcelOperation excle = new ExcelOperation();
		excle.export(filetitle, rowsName, columnTop, Result, response);
	}

	/**
	 * 查询需要导出的数据
	 * @param departmentType
	 * @param projectType
	 * @param startdate
	 * @param enddate
	 * @return
	 */
	public Response getExportResponse(String departmentType, String projectType, String startdate, String enddate) {

		Response response = new Response();
		response.setSuccess(false);
		String Result = null;
		String selectstr = "";
		try {
			if ("company".equals(departmentType)) {
				selectstr = "  select tf.organization_name company, tf.displayname as TEAMLEADER,tf.gnum,th.pnum,tf.gnum*2 zhibiao, ROUND((NVL(th.pnum,0)/(tf.gnum*2))*100,1) wcd "
						+ "  from (select t5.*, t4.organization_name ,t6.displayname                                                                                      "
						+ "             from (select t.parent_id, sum(t.gnum) gnum                                                                         "
						+ "                     from (select t1.id,                                                                                        "
						+ "                                  t1.organization_name,                                                                         "
						+ "                                  t1.parent_id,                                                                                 "
						+ "                                  tb.gnum                                                                                       "
						+ "                             from SDDCGISPLATFORM.ORGANIZATION t1,                                                                              "
						+ "                                  (select t3.parent_id,                                                                         "
						+ "                                          count(t3.parent_id) gnum                                                              "
						+ "                                     from SDDCGISPLATFORM.ORGANIZATION t3                                                                       "
						+ "                                    where t3.organization_type = '3'                                                            "
						+ "                                    group by t3.parent_id) tb                                                                   "
						+ "                            where t1.id = tb.parent_id                                                                          "
						+ "                              and t1.organization_type = '2') t                                                                 "
						+ "                    group by t.parent_id) t5,                                                                                   "
						+ "                  SDDCGISPLATFORM.ORGANIZATION t4,  SDDCONEMAP.USERINFO  t6                                                                                      "
						+ "            where t4.id = t5.parent_id and t4.organization_manager_id=t6.userid(+)) tf,                                                                                     "
						+ "  (select tg.companyid,tg.company,count(tg.company) pnum from                                                                   "
						+ "    (select tc.id companyid,                                                                                                    "
						+ "    tc.organization_name company                                                                                                "
						+ "     from SDE.LANDEXPAND t,                                                                                                         "
						+ "          SDDCGISPLATFORM.USER_ORGANIZATION t2,                                                                                                 "
						+ "          SDDCONEMAP.USERINFO t1,                                                                                                          "
						+ "          SDDCGISPLATFORM.ORGANIZATION tc                                                                                                       "
						+ "    where t.registeruser = t2.user_id(+)                                                                                        "
						+ "      and t.registeruser = t1.userid(+)                                                                                         "
						+ "      and tc.id = t2.organization_company_id                                                                                    "
						+ "      and t.type = '" + projectType + "'                                         "
						+ "      and t.registerdate >= to_date('" + startdate + "', 'yyyy-mm-dd')           "
						+ "      and t.registerdate <= to_date('" + enddate
						+ "', 'yyyy-mm-dd')) tg                                                            "
						+ "      group by tg.companyid,tg.company) th                                                                                      "
						+ "      where tf.parent_id = th.companyid(+) order by wcd desc ";
			} else if ("department".equals(departmentType)) {

				selectstr = "  select tf.company,tf.department,tf.displayname as TEAMLEADER,tf.gnum, tt.pnum,tf.gnum*2 zhibiao,ROUND((NVL(tt.pnum,0)/(tf.gnum*2))*100,1)  wcd  from    "
						+ " (select t2.organization_name company, t1.organization_name department,t1.id departmentid,tb.gnum ,t6.displayname                                       "
						+ "                  from SDDCGISPLATFORM.ORGANIZATION t1,                                                                                                 "
						+ "                       SDDCGISPLATFORM.ORGANIZATION t2,                                                                                                 "
						+ "                       (select t3.parent_id, count(t3.parent_id) gnum                                                                   "
						+ "                          from SDDCGISPLATFORM.ORGANIZATION t3                                                                                          "
						+ "                         where t3.organization_type = '3'                                                                               "
						+ "                         group by t3.parent_id) tb  ,SDDCONEMAP.USERINFO t6                                                                                    "
						+ "                 where t1.id = tb.parent_id                                                                                             "
						+ "                   and t2.id = t1.parent_id                                                                                             "
						+ "                   and t1.organization_type = '2' and t1.organization_manager_id=t6.userid(+))tf,                                                                                   "
						+ " (select tg.company,tg.department,tg.departmentid,count(tg.department) pnum from                                                        "
						+ "    (select t.objectid,                                                                                                                 "
						+ "          t.xh,                                                                                                                         "
						+ "          t.type,                                                                                                                       "
						+ "          t.registeruser,                                                                                                               "
						+ "          t1.displayname,                                                                                                               "
						+ "          tb.organization_name department,                                                                                              "
						+ "          tb.id departmentid,                                                                                                           "
						+ "          tc.organization_name company                                                                                                  "
						+ "     from SDE.LANDEXPAND t,                                                                                                                 "
						+ "          SDDCGISPLATFORM.USER_ORGANIZATION t2,                                                                                                         "
						+ "          SDDCONEMAP.USERINFO t1,                                                                                                                  "
						+ "          SDDCGISPLATFORM.ORGANIZATION tz,                                                                                                              "
						+ "          SDDCGISPLATFORM.ORGANIZATION tb,                                                                                                              "
						+ "          SDDCGISPLATFORM.ORGANIZATION tc                                                                                                               "
						+ "    where t.registeruser = t2.user_id(+)                                                                                                "
						+ "      and t.registeruser = t1.userid(+)                                                                                                 "
						+ "      and tz.id = t2.organization_develop_id                                                                                            "
						+ "      and tb.id = t2.organization_business_id                                                                                           "
						+ "      and tc.id = t2.organization_company_id                                                                                            "
						+ "      and t.type = '" + projectType + "'                                         "
						+ "      and t.registerdate >= to_date('" + startdate + "', 'yyyy-mm-dd')           "
						+ "      and t.registerdate <= to_date('" + enddate
						+ "', 'yyyy-mm-dd')) tg                                                                     "
						+ "      group by tg.department,tg.departmentid,tg.company) tt                                                                             "
						+ "      where tf.departmentid = tt.departmentid(+) order by wcd desc                                                                     ";
			} else if ("teamgroup".equals(departmentType)) {
				selectstr = " select tf.company,tf.department,tf.team,tf.teamleader,th.pnum,'2' zhibiao,(NVL(th.pnum,0)/2)*100  wcd from     "
						+ " (select tc.organization_name company,tb.organization_name department,tt.organization_name team ,tt.id teamid,"
						+ "(select tu.displayname from SDDCONEMAP.USERINFO tu where tu.userid = tt.organization_manager_id) teamleader  from  "
                           
                          
						+ "          SDDCGISPLATFORM.ORGANIZATION tt,                                                                                          "
						+ "          SDDCGISPLATFORM.ORGANIZATION tb,                                                                                          "
						+ "          SDDCGISPLATFORM.ORGANIZATION tc                                                                                           "
						+ "    where tt.parent_id = tb.id                                                                                      "
						+ "      and tb.parent_id = tc.id) tf,                                                                                 "
						+ " (select tg.company,tg.department,tg.teamid,tg.team,count(tg.team) pnum from                          "
						+ "    (select t.objectid,                                                                                             "
						+ "          t.xh,                                                                                                     "
						+ "          t.type,                                                                                                   "
						+ "          t.registeruser,                                                                                           "
						+ "          t1.displayname,                                                                                           "
						+ "          tz.organization_name team,                                                                                "
						+ "          tz.id teamid,                                                                                             "
						+ "          tb.organization_name department,                                                                          "
						+ "          tb.id departmentid,                                                                                       "
						+ "          tc.organization_name company,                                                                             "
						+ "          tc.id companyid                                                                                           "
						+ "     from SDE.LANDEXPAND t,                                                                                             "
						+ "          SDDCGISPLATFORM.USER_ORGANIZATION t2,                                                                                     "
						+ "          SDDCONEMAP.USERINFO t1,                                                                                              "
						+ "          SDDCGISPLATFORM.ORGANIZATION tz,                                                                                          "
						+ "          SDDCGISPLATFORM.ORGANIZATION tb,                                                                                          "
						+ "          SDDCGISPLATFORM.ORGANIZATION tc                                                                                           "
						+ "    where t.registeruser = t2.user_id(+)                                                                            "
						+ "      and t.registeruser = t1.userid(+)                                                                             "
						+ "      and tz.id = t2.organization_develop_id                                                                        "
						+ "      and tb.id = t2.organization_business_id                                                                       "
						+ "      and tc.id = t2.organization_company_id                                                                        "
						+ "      and t.type = '" + projectType + "'                                         "
						+ "      and t.registerdate >= to_date('" + startdate + "', 'yyyy-mm-dd')           "
						+ "      and t.registerdate <= to_date('" + enddate + "', 'yyyy-mm-dd')) tg         "
						+ "      group by tg.teamid,tg.team,tg.department,tg.company) th  "
						+ "      where tf.teamid = th.teamid(+) order by wcd desc   ";
			}

			Result = landStatisticsDao.queryAndStatisticsLandByTeam(selectstr);

			if (Result == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}

	//通过人员所在组织个数来统计指标
	public Response queryTaskTarget() {

		Response response = new Response();
		response.setSuccess(false);
		String Result = null;
		StringBuffer selectstr = null;
		try {

			selectstr =  new StringBuffer("select t1.organization_name as companyName,t2.organization_name as businessName,t3.organization_name as developName from SDDCGISPLATFORM.user_organization t ");
			selectstr.append(" left join SDDCGISPLATFORM.ORGANIZATION t1 on  t.organization_company_id = t1.id ");
			selectstr.append(" left join SDDCGISPLATFORM.ORGANIZATION t2 on  t.organization_business_id  = t2.id ");
			selectstr.append(" left join SDDCGISPLATFORM.ORGANIZATION t3 on  t.organization_develop_id = t3.id ");
			//添加过滤,负责人不能算入指标内 
			selectstr.append("  where t.user_id not in (select distinct(tu.organization_manager_id) from SDDCGISPLATFORM.organization tu where tu.organization_manager_id is not null) ");
			// 拓展组ID不能为空
			selectstr.append("  and t.organization_develop_id is not null");
			Result = landStatisticsDao.queryAndStatisticsLandByTeam(selectstr.toString());

			if (Result == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}
	
	//通过组织中配置的指标来取值
	public Response queryTaskTargetByOrgTarget() {

		Response response = new Response();
		response.setSuccess(false);
		String Result = null;
		StringBuffer selectstr = null;
		try {
			/**
			 select org_id,wytz_type,to_number(target) target,'3' orgType from v_org_develop_target t where t.target is not null
			union
			select org_id,wytz_type,target,'2' orgType from v_org_business_target t where t.target is not null
			union
			select org_id,wytz_type,target,'1' orgType from v_org_company_target t where t.target is not null

			 */
			selectstr =  new StringBuffer();
			selectstr.append(" select org_id,wytz_type,to_number(target) target,'3' orgType from sddcgisplatform.v_org_develop_target t where t.target is not null union ");
			selectstr.append(" select org_id,wytz_type,target,'2' orgType from sddcgisplatform.v_org_business_target t where t.target is not null union ");
			selectstr.append(" select org_id,wytz_type,target,'1' orgType from sddcgisplatform.v_org_company_target t where t.target is not null");
 
			Result = landStatisticsDao.queryAndStatisticsLandByTeam(selectstr.toString());

			if (Result == null) {
				return response;
			}
			response.setSuccess(true);
			response.setResult(Result);
			return response;
		} catch (Exception e) {
			log.error(e.getMessage());
			return response;
		}
	}

	public void exportWYTZInfo(String ids,String tableName,HttpServletResponse response) throws Exception {
		String filetitle = "外业拓展详细信息";
		String[] rowsName = null;
		String[] columnTop = null;
		String rowTitle="";
 
		if(!StringUtil.isNullOrEmpty(ids)){
			Response responseStr = getWYTZExportResponse(ids,tableName);
			 
			 
			rowsName = new String[] { "序号","OBJECTID","项目阶段","城市地图地块编码","地块名称","市","区","镇街","具体地址","项目属性","拓展方式","土规信息","控规信息","现状建设信息","总用地面积（平方米）","净用地面积（平方米）","地块容积率","所有权信息","权属单位","规划证号","国土证号","联系人属性","联系人姓名","联系方式","保证金","总地价","登记日期","地区公司","拓展事业部","拓展组","预计签约时间（合作/收购合同）","预计获取时间（完成收购/土地获取）","实际签约时间（合作/收购合同 ）实际签约时间（合作/收购合同 ）","实际获取时间（完成收购/土地获取 ）","备注","用户名称"};
			columnTop = new String[] { "","OBJECTID", "XMJD",  "DKBM","DKMC", "CS","QJ", "ZJ","DZ","XMSX","TZFS","TGXX","KGXX","XZJSXX","ZYDMJ","JYDMJ","DKRJL","SYQXX","QSDW","GHZH","GTZH","LXRSX","LXRXM","LXFS","BZJ","ZDJ","DJRQ","DQGS","TZSYB","TZZ","YJQYSJ","YJHQSJ","SJQYSJ","SJHQSJ","BZ","USERNAME" };
		 
//		if(responseStr.getResult().)
		String resultStr = (String) responseStr.getResult();
		JSONArray resultJsonArr = new JSONArray(resultStr);
		resultJsonArr.length();
		List<Map> Result = new ArrayList<Map>();
		if (resultJsonArr != null && resultJsonArr.length() > 0) {

			for (int i = 0; i < resultJsonArr.length(); i++) {
				JSONObject ret = (JSONObject) resultJsonArr.get(i);
				Iterator iterator = ret.keys();
				Map map = new HashMap();
				while (iterator.hasNext()) {
					String key = (String) iterator.next();
					String value = (String) ret.get(key);
					if("null".equals(value)||"2011-11-11 00:00:00.0".equals(value)){
				      value="";
					}
					map.put(key, value);
				}
				Result.add(map);
			}
		}
		ExcelOperation excle = new ExcelOperation();
		excle.export(filetitle, rowsName, columnTop, Result, response);
		}
		
	}

	private Response getWYTZExportResponse(String ids,String tableName) {
		 Response response = new Response();
		 StringBuffer sbf = new  StringBuffer();
		 sbf.append( "select ");
		 sbf.append("nvl(OBJECTID,0) OBJECTID ,");
		 sbf.append("nvl(XMJD,'null') XMJD ,");
		 sbf.append("nvl(DKBM,'null') DKBM ,");
		 sbf.append("nvl(DKMC,'null') DKMC ,");
		 sbf.append("nvl(CS,'null') CS ,");
		 sbf.append("nvl(QJ,'null') QJ ,");
		 sbf.append("nvl(ZJ,'null') ZJ ,");
		 sbf.append("nvl(DZ,'null') DZ ,");
		 sbf.append("nvl(XMSX,'null') XMSX ,");
		 sbf.append("nvl(TZFS,'null') TZFS ,");
		 sbf.append("nvl(TGXX,'null') TGXX ,");
		 sbf.append("nvl(KGXX,'null') KGXX ,");
		 sbf.append("nvl(XZJSXX,'null') XZJSXX ,");
		 sbf.append("nvl(ZYDMJ,0) ZYDMJ ,");
		 sbf.append("nvl(JYDMJ,0) JYDMJ ,");
		 sbf.append("nvl(DKRJL,'null') DKRJL ,");
		 sbf.append("nvl(SYQXX,'null') SYQXX ,");
		 sbf.append("nvl(QSDW,'null') QSDW ,");
		 sbf.append("nvl(GHZH,'null') GHZH ,");
		 sbf.append("nvl(GTZH,'null') GTZH ,");
		 sbf.append("nvl(LXRSX,'null') LXRSX ,");
		 sbf.append("nvl(LXRXM,'null') LXRXM ,");
		 sbf.append("nvl(LXFS,'null') LXFS ,");
		 sbf.append("nvl(BZJ,'null') BZJ ,");
		 sbf.append("nvl(ZDJ,'null') ZDJ ,");
		 sbf.append("nvl(DJRQ,to_date('20111111','yyyyMMdd')) DJRQ ,");
		 sbf.append("nvl(DQGS,'null') DQGS ,");
		 sbf.append("nvl(TZSYB,'null') TZSYB ,");
		 sbf.append("nvl(TZZ,'null') TZZ ,");
		 sbf.append("nvl(YJQYSJ,to_date('20111111','yyyyMMdd')) YJQYSJ ,");
		 sbf.append("nvl(YJHQSJ,to_date('20111111','yyyyMMdd')) YJHQSJ ,");
		 sbf.append("nvl(SJQYSJ,to_date('20111111','yyyyMMdd')) SJQYSJ ,");
		 sbf.append("nvl(SJHQSJ,to_date('20111111','yyyyMMdd')) SJHQSJ ,");
		 sbf.append("nvl(BZ,'null') BZ ,");
		 sbf.append("nvl(USERNAME,'null') USERNAME ");
		 sbf.append( " from "+tableName+" where objectid in ("+ids+")");
		 response.setResult(landStatisticsDao.queryAndStatisticsLandByTeam(sbf.toString()));
	    	return response;
	}
	
}
